"""
    This function will extract Glassdoor's educational histories.
"""

import pandas as pd

def extractResumeEduc(conn):

    query = '''
                select
                    		FK_resumeId, FK_userId,
                    		coalesce(un.name, re.userEnteredSchool) as schoolName,
                    	    coalesce(um.name, re.userEnteredMajor) as majorName,
                        userEnteredDegree as degreeName,
                        startDate as startSchool,
                        endDate as endSchool,
                        gpa,
                        gender,
                        birthYear,
                        highestEducation
                from userProfile..resume r with(nolock)
                left join 
                    userProfile..resumeEducation re with(nolock) 
                    on re.FK_resumeId = r.PK_id
                left join 
                    bizproj..universityname un with (nolock) 
                    on re.FK_schoolId = un.PK_id
                left join 
                    bizproj..universitymajor um with (nolock) 
                    on re.FK_majorId = um.PK_id
                where FK_userId > 0 and FK_resumeId is not null
            '''

    return pd.read_sql(query,conn)
